package mysql;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestExcel2 {
    public static void add(String path) {

        String jdbcURL = "jdbc:mysql://localhost:3306/authorization";
        String username = "root";
        String password = "12345678";
        String filePath = path;
        String excelFilePath = filePath;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            Connection connection = DriverManager.getConnection(jdbcURL, username, password);



            FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(1);
            int rowCount = sheet.getLastRowNum();


            String sql = "INSERT INTO authorization_code (a_code,level,state) VALUES (?,?,0)";
            PreparedStatement statement = connection.prepareStatement(sql);
            for (int row = 0; row <= rowCount; row++) {
                Row rowObj = sheet.getRow(row);
                if (rowObj != null) {


                    String column1 = rowObj.getCell(0).getStringCellValue();
                    statement.setString(1, column1);

                    if(row%3==0){
                        statement.setInt(2, 0);
                    }
                    else if(row%3==1){
                        statement.setInt(2, 1);
                    }
                    else {
                        statement.setInt(2, 2);
                    }


                    statement.executeUpdate();
                }
            }
            workbook.close();
            statement.close();
        } catch (SQLException | IOException |ClassNotFoundException ex) {
            ex.printStackTrace();
            System.out.println("请手动添加授权码");
        }
    }
}
